using System;
using System.Collections;
using System.Data;
using System.IO;
using PHSRAG.Insight21.Common.Pages;
using PHSRAG.Insight21.Context;
using PHSRAG.Insight21.Policy;
using PHSRAG.Utility;
using Utils = PHSRAG.Insight21.InsightUtilities;
using PersonnelBO = PHSRAG.Insight21.Personnel;
using PerDetailConstants = PHSRAG.Insight21.Personnel.Constants.PersonnelDetailsConstants;
using PersonnelConstants = PHSRAG.Insight21.Personnel.Constants;

namespace PHSRAG.Insight21.Personnel.Reports
{
    public partial class SQLReportHost : BasePage
    {
        #region Member Variables

        private ICachePolicy cachePolicy;
        private ICachePolicy pageCachePolicy;
        private string searchParameters = string.Empty;

        #endregion

        #region Properties

        /// <summary>
        /// Get the personnel summary informations that the logged-in user has access to. The list of personnel summaries is
        /// is maintained by the PersonnelSummaries business object within its data container. This object is cached.
        /// </summary>
        private PersonnelSummaries PersonnelSummariesInstance
        {
            get
            {
                PersonnelSummaries personnelSummaries =
                    ResultsCache.GetObject(cachePolicy, PersonnelSummaries.CacheKey) as PersonnelSummaries;

                if (personnelSummaries == null)
                {
                    personnelSummaries = new PersonnelSummaries();
                    personnelSummaries.Load(DataAccess.GetConnectionString()
                        , ((UserContext) cachePolicy.GetCachedObject(UserContext.CacheKey)).DomainUserId);
                    ResultsCache.SetObject(cachePolicy, PersonnelSummaries.CacheKey, personnelSummaries);
                }

                return personnelSummaries;
            }
        }

        /// <summary>
        /// Get/Set the Employee Job Map ID for which the details information has to be retrieved.
        /// </summary>
        private int JobMapId
        {
            get
            {
                Hashtable h = pageCachePolicy.GetCachedObject(Keys.PersonnelDetailsPageKey) as Hashtable;
                return (h == null || h[Keys.EmployeeJobMapIDKey] == null) ? 0 : (int) h[Keys.EmployeeJobMapIDKey];
            }
        }

        /// <summary>
        /// Get/Set the fiscal year ID for which the personnel details information has to be retrieved
        /// </summary>
        private int FiscalYearId
        {
            get
            {
                Hashtable h = pageCachePolicy.GetCachedObject(Keys.PersonnelDetailsPageKey) as Hashtable;
                return (h == null || h[Keys.FiscalYearIDKey] == null) ? 0 : (int) h[Keys.FiscalYearIDKey];
            }
        }

        /// <summary>
        /// Get the personnel details informations that the logged-in user has access to.This object is cached.
        /// </summary>
        private PersonnelDetails PersonnelDetailsInstance
        {
            get
            {
                try
                {
                    PersonnelDetails perDetails =
                        ResultsCache.GetObject(cachePolicy, PersonnelDetails.CacheKey) as PersonnelDetails;

                    if (perDetails == null)
                    {
                        perDetails = new PersonnelDetails();
                        perDetails.Load(DataAccess.GetConnectionString()
                            , ((UserContext) cachePolicy.GetCachedObject(UserContext.CacheKey)).DomainUserId
                            , JobMapId, FiscalYearId);
                        ResultsCache.SetObject(cachePolicy, PersonnelDetails.CacheKey, perDetails);
                    }

                    return perDetails;
                }
                catch (Exception ex)
                {
                    throw new Exception(@"Error in loading Personnel Details", ex);
                }
            }
        }

        /// <summary>
        /// Get the Salary Grid informations that the logged-in user has access to. The list of Salary Grids data is
        /// is maintained by the PersonnelSalaries business object within its data container. This object is cached.
        /// </summary>
        private PersonnelSalaries PersonnelSalariesInstance
        {
            get
            {
                PersonnelSalaries personnelSalaries =
                    ResultsCache.GetObject(cachePolicy, PersonnelSalaries.CacheKey) as PersonnelSalaries;

                if (personnelSalaries == null)
                {
                    personnelSalaries = new PersonnelSalaries();
                    personnelSalaries.Load(DataAccess.GetConnectionString()
                        , ((UserContext) cachePolicy.GetCachedObject(UserContext.CacheKey)).DomainUserId
                        , FiscalYearId
                        , JobMapId);
                    ResultsCache.SetObject(cachePolicy, PersonnelSalaries.CacheKey, personnelSalaries);
                }

                return personnelSalaries;
            }
        }

        /// <summary>
        /// Get the expired fund allocation information the currently logged in user has access to.The list of
        /// expired fund allocations is maintained by the ExpiredFundAllocations instance within its data container
        /// This object is cached.
        /// </summary>
        private ExpiredFundAllocations FundAllocationsInstance
        {
            get
            {
                ExpiredFundAllocations expiredFundAlloc =
                    ResultsCache.GetObject(cachePolicy, ExpiredFundAllocations.CacheKey) as ExpiredFundAllocations;

                if (expiredFundAlloc == null)
                {
                    expiredFundAlloc = new ExpiredFundAllocations();
                    expiredFundAlloc.Load(DataAccess.GetConnectionString()
                        , ((UserContext) cachePolicy.GetCachedObject(UserContext.CacheKey)).DomainUserId);

                    ResultsCache.SetObject(cachePolicy, ExpiredFundAllocations.CacheKey, expiredFundAlloc);
                }

                pageCachePolicy.Cache(Keys.ExcelRecordCountKey, expiredFundAlloc.Count);
                return expiredFundAlloc;
            }
        }

        /// <summary>
        /// Get the exception informations that the logged-in user has access to. The list of payroll exceptions is
        /// is maintained by the PayrollExceptions business object within its data container. This object is cached.
        /// </summary>
        private PayrollExceptions PayrollExceptionsInstance
        {
            get
            {
                PayrollExceptions payrollExceptions =
                    ResultsCache.GetObject(cachePolicy, PayrollExceptions.CacheKey) as PayrollExceptions;

                if (payrollExceptions == null)
                {
                    payrollExceptions = new PayrollExceptions();
                    payrollExceptions.Load(DataAccess.GetConnectionString()
                        , ((UserContext) cachePolicy.GetCachedObject(UserContext.CacheKey)).DomainUserId
                        , PayrollDate);
                    ResultsCache.SetObject(cachePolicy, PayrollExceptions.CacheKey, payrollExceptions);
                }

                return payrollExceptions;
            }
        }

        /// <summary>
        /// Gets/Sets the payroll report date. 
        /// Returns an empty string if the date is not available in the page cache.
        /// Sets the selected date in the page cache.
        /// </summary>
        private string PayrollDate
        {
            get
            {
                Hashtable h = pageCachePolicy.GetCachedObject(Keys.PayrollReportPageKey) as Hashtable;
                return (h == null || h[Keys.ReportDateKey] == null) ? string.Empty : (string) h[Keys.ReportDateKey];
            }
            set
            {
                Hashtable h = pageCachePolicy.GetCachedObject(Keys.PayrollReportPageKey) as Hashtable ?? new Hashtable();
                h[Keys.ReportDateKey] = value;
                pageCachePolicy.Cache(Keys.PayrollReportPageKey, h);
            }
        }

        #endregion

        #region Event Handlers

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sender">Object that raised the event</param>
        /// <param name="e">Arguments associated with the event</param>
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                try
                {
                    string srptName = Request["rpt"]; //name of the report to serve up
                    string format = Request["fmt"]; //format of the report - PDF or Excel
                    searchParameters = Request["searchParameters"];
                    if (!string.IsNullOrEmpty(srptName))
                    {
                        SSRSRequest.FetchAndDisplayReport(format, srptName, GetInputParameters(ref srptName));
                    }
                }
                catch (Exception ex)
                {
                    Response.Write("There was a problem processing your request. Please contact helpdesk. <br>" + ex.Message);
                }
            }
        }

        /// <summary>
        /// Handler for the event raised when the page is being initialized.
        /// Retrieve the cache policy and page cache policy in force.
        /// </summary>
        /// <param name="e">Argument associated with the event</param>
        protected override void OnInit(EventArgs e)
        {
            cachePolicy = (ICachePolicy) Session[Names.CachePolicyKey];
            pageCachePolicy = (ICachePolicy) Session[Names.PageCachePolicyKey];

            //InitializeComponent();
            base.OnInit(e);
        }

        #endregion

        # region InputParameters

        private Hashtable GetInputParameters(ref string srptName)
        {
            Hashtable tbl = new Hashtable();
            switch (srptName.ToLower())
            {
                case SSRSRequest.ReportNames.Personnel.PersonnelSummary: //"personnel_personnelsummary":
                {
                    DataSet activeDataSet = new DataSet();
                    PersonnelSummaries personnelSummariesInstance = PersonnelSummariesInstance;
                    activeDataSet.Tables.Add
                        (personnelSummariesInstance.GetDataContainer().
                            Tables[PersonnelSummariesInstance.CurrentDataTableName].Copy());

                    tbl.Add(SSRSRequest.ReportParameterKeys.DataSource, activeDataSet.GetXml());
                    tbl.Add(SSRSRequest.ReportParameterKeys.FilterCriteria,
                        searchParameters ?? string.Empty);
                }
                    break;
                case SSRSRequest.ReportNames.Personnel.PersonnelDetails: //"personnel_personneldetails":
                {
                    DataSet personnelDetailsDataSet = PersonnelDetailsInstance.GetDataContainer();

                    DataSet reportDataSet = new DataSet();
                    if (personnelDetailsDataSet.Tables.Contains(PerDetailConstants.PersonnelDetailsTableName))
                    {
                        reportDataSet.Tables.Add(personnelDetailsDataSet.Tables[PerDetailConstants.PersonnelDetailsTableName].Copy());
                    }
                    if (personnelDetailsDataSet.Tables.Contains(Constants.SalaryAdjustmentsConstants.SalaryAdjustmentsTableName))
                    {
                        reportDataSet.Tables.Add(personnelDetailsDataSet.Tables[Constants.SalaryAdjustmentsConstants.SalaryAdjustmentsTableName].Copy());
                    }
                    reportDataSet.Tables.Add(BuildSplitsDataTableForReports(true));
                    if (personnelDetailsDataSet.Tables.Contains(PerDetailConstants.CommentsTableName))
                    {
                        reportDataSet.Tables.Add(personnelDetailsDataSet.Tables[PerDetailConstants.CommentsTableName].Copy());
                    }
                    else
                    {
                        DataTable commentsTable = new DataTable();
                        commentsTable.TableName = PerDetailConstants.CommentsTableName;
                        commentsTable.Columns.Add("Comment");
                        commentsTable.Columns.Add("EditedBy");
                        commentsTable.Columns.Add("EditDate");
                        reportDataSet.Tables.Add(commentsTable);
                    }
                    reportDataSet.Tables.Add(BuildWarningDataTableForReports());

                    StringWriter sw = new StringWriter();
                    reportDataSet.WriteXml(sw, XmlWriteMode.WriteSchema);
                    tbl.Add(SSRSRequest.ReportParameterKeys.DataSource, sw.ToString());

                    string[] filterCriteria = null;
                    if (searchParameters != null)
                    {
                        filterCriteria = searchParameters.Split('|');
                    }

                    tbl.Add(SSRSRequest.ReportParameterKeys.FilterCriteria,
                        string.Format("Fiscal Year | {0}", (filterCriteria.Length > 0) ? filterCriteria[0] : string.Empty));

                    tbl.Add(SSRSRequest.ReportParameterKeys.SalaryAsOfDate,
                        (filterCriteria.Length > 1) ? filterCriteria[1] : string.Empty);
                }
                    break;
                case SSRSRequest.ReportNames.Personnel.SalaryGrid: //"personnel_salarygrid":
                {
                    //TODO: remove cloning datatable
                    DataSet sg_activeDataSet = new DataSet();
                    PersonnelSalaries personnelSalariesInstance = PersonnelSalariesInstance;
                    sg_activeDataSet.Tables.Add
                        (personnelSalariesInstance.GetDataContainer().
                            Tables[PersonnelSalariesInstance.CurrentDataTableName].Copy());

                    StringWriter sg_sw = new StringWriter();
                    sg_activeDataSet.WriteXml(sg_sw, XmlWriteMode.WriteSchema);
                    tbl.Add(SSRSRequest.ReportParameterKeys.DataSource, sg_sw.ToString());

                    tbl.Add(SSRSRequest.ReportParameterKeys.FilterCriteria,
                        searchParameters ?? string.Empty);
                }
                    break;
                case SSRSRequest.ReportNames.Personnel.ExceptionReport: //"personnel_exceptionreport":
                {
                    DataSet er_activeDataSet = new DataSet();
                    er_activeDataSet.Tables.Add
                        (PayrollExceptionsInstance.GetDataContainer().
                            Tables[PayrollExceptionsInstance.CurrentDataTableName].Copy());
                    er_activeDataSet.Tables[0].TableName = "ExceptionReport";


                    StringWriter er_sw = new StringWriter();
                    er_activeDataSet.WriteXml(er_sw, XmlWriteMode.WriteSchema);
                    tbl.Add(SSRSRequest.ReportParameterKeys.DataSource, er_sw.ToString());

                    tbl.Add(SSRSRequest.ReportParameterKeys.FilterCriteria,
                        searchParameters ?? string.Empty);
                }
                    break;

                case SSRSRequest.ReportNames.Personnel.PayrollReport:
                {
                    tbl.Add("reportdate", DateTime.Parse(searchParameters).ToShortDateString());
                    UserContext uc = (UserContext) Session[UserContext.CacheKey];
                    tbl.Add("domainuserid", uc.DomainUserId.ToString());
                    tbl.Add("reporttype", "");
                    tbl.Add("exceptionreportflag", null);
                }
                    break;

                case SSRSRequest.ReportNames.Personnel.ExpiredFundAllocation: //"personnel_expiredfundallocation":
                {
                    //TODO: remove cloning datatable
                    DataSet efa_activeDataSet = new DataSet();
                    efa_activeDataSet.Tables.Add
                        (FundAllocationsInstance.GetDataContainer().
                            Tables[FundAllocationsInstance.CurrentDataTableName].Copy());
                    efa_activeDataSet.Tables[0].TableName = "ExpiredFundAllocation";

                    StringWriter efa_sw = new StringWriter();
                    efa_activeDataSet.WriteXml(efa_sw, XmlWriteMode.WriteSchema);
                    tbl.Add(SSRSRequest.ReportParameterKeys.DataSource, efa_sw.ToString());

                    tbl.Add(SSRSRequest.ReportParameterKeys.FilterCriteria,
                        searchParameters ?? string.Empty);
                }

                    break;
            }

            UserContext userContext = (UserContext) Session[UserContext.CacheKey];
            tbl.Add(SSRSRequest.ReportParameterKeys.UserName, userContext.LastName + "," + userContext.FirstName);

            tbl.Add(SSRSRequest.ReportParameterKeys.WebUrl, Settings.GetKeyValue("weburl", "http://insight2.partners.org"));

            return tbl;
        }

        #endregion

        #region Personnel Details

        /// <summary>
        /// Creates a splits datatable for split details subreport.
        /// </summary>
        /// <param name="addSeparatorRows">Whether to add separator row or not before 
        /// and after the "Totals" row.</param>
        /// <returns>Split detail datatable.</returns>
        private DataTable BuildSplitsDataTableForReports(bool addSeparatorRows)
        {
            DataTable splitsTable = new DataTable("SalarySplits");
            AddTableColumnsForReports(splitsTable);
            int splitDistribID = 0; // flag used by crystal reports to group data
            foreach (DistributionPeriod distribPeriod in PersonnelDetailsInstance.DistribPeriods)
            {
                DataTable splitsSubTable = splitsTable.Clone();
                foreach (DistributionSplit split in distribPeriod.Splits)
                {
                    //AddDescriptorRowToSplitTable(splitsTable, distribPeriod);
                    SalaryDistribution salaryDistribution =
                        PersonnelDetailsInstance.SalaryDistribs.GetSalaryDistribution(split.SplitID);
                    object[] splitsRow = new object[]
                                             {
                                                 splitDistribID, split.FundNumber, split.ProjectTitle, split.AnnualRate,
                                                 split.EffectiveDate, split.ExpirationDate, split.PercentEffort,
                                                 split.ToBePaid, split.EditedBy, split.EditDate, salaryDistribution.Comment
                                             };
                    splitsTable.Rows.Add(splitsRow);
                    splitsSubTable.Rows.Add(splitsRow);
                }
                //if (distribPeriod.Splits.Count > 0)
                //    AddSumRowToSplitsTable(splitsTable, splitsSubTable, addSeparatorRows);

                ++splitDistribID;
            }
            splitsTable.AcceptChanges();
            return splitsTable;
        }


        /// <summary>
        /// Adds columns needed by the split details subreport.
        /// </summary>
        /// <param name="table"></param>
        private void AddTableColumnsForReports(DataTable table)
        {
            table.Reset();
            table.Columns.Add("SplitId", typeof (int));
            table.Columns.Add("FundNumber", typeof (string));
            table.Columns.Add("ProjectTitle", typeof (string));
            table.Columns.Add("AnnualRate", typeof (decimal));
            table.Columns.Add("EffectiveDate", typeof (string));
            table.Columns.Add("ExpirationDate", typeof (string));
            table.Columns.Add("PercentEffort", typeof (decimal));
            table.Columns.Add("ToBePaid", typeof (decimal));
            table.Columns.Add("EditedBy", typeof (string));
            table.Columns.Add("EditDate", typeof (string));
            table.Columns.Add("Comment", typeof (string));
        }

        /// <summary>
        /// Adds a sum row in the end of the distribution period in the split
        /// details datatable. It also adds a separator row before and after
        /// adding a sum row.
        /// </summary>
        /// <param name="splitsTable">Split details datatable.</param>
        /// <param name="splitsSubTable">A table with a subset of splittable details
        /// for a specific distribution period.</param>
        /// <param name="addSeparatorRows">Whether to add separator row or not.</param>
        private void AddSumRowToSplitsTable(DataTable splitsTable, DataTable splitsSubTable, bool addSeparatorRows)
        {
            object[] separatorRow = new object[]
                                        {
                                            null, "------", "-------------", null,
                                            null, null, null,
                                            null, null, null, null
                                        };
            // add an empty row
            if (addSeparatorRows)
            {
                splitsTable.Rows.Add(separatorRow);
            }
            splitsTable.Rows.Add(new object[]
                                     {
                                         null, "Totals", null, splitsSubTable.Compute("sum(AnnualRate)", string.Empty),
                                         null, null, splitsSubTable.Compute("sum(PercentEffort)", string.Empty),
                                         splitsSubTable.Compute("sum(ToBePaid)", string.Empty), null, null, null
                                     }
                );

            // add another empty row
            if (addSeparatorRows)
            {
                splitsTable.Rows.Add(separatorRow);
            }
        }

        /// <summary>
        /// Creates a salary distribution warning datatable for crystal report
        /// warning subreport. 
        /// </summary>
        /// <returns>Warning datatable.</returns>
        private DataTable BuildWarningDataTableForReports()
        {
            DataTable warnings = new DataTable("Warnings");
            warnings.Columns.Add("Warning", typeof (string));
            foreach (DistributionPeriod distribPeriod in PersonnelDetailsInstance.DistribPeriods)
            {
                string allocationWarning = distribPeriod.AllocationWarning;
                if (allocationWarning != string.Empty)
                {
                    DataRow allocWarningRow = warnings.NewRow();
                    allocWarningRow["Warning"] = allocationWarning;
                    warnings.Rows.Add(allocWarningRow);
                }
                string effortWarning = distribPeriod.EffortWarning;
                if (effortWarning != string.Empty)
                {
                    DataRow effortWarningRow = warnings.NewRow();
                    effortWarningRow["Warning"] = effortWarning;
                    warnings.Rows.Add(effortWarningRow);
                }
            }
            if (warnings.Rows.Count == 0)
            {
                DataRow noErrorRow = warnings.NewRow();
                noErrorRow["Warning"] = "No Allocation/Effort Warnings Found.";
                warnings.Rows.Add(noErrorRow);
            }
            return warnings;
        }

        #endregion
    }
}